IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[IE_SeedSvcEntityStaffedPersonMap]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[IE_SeedSvcEntityStaffedPersonMap]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



/***************************************************************************
* Name:			IE_SeedSvcEntityStaffedPersonMap
* Purpose:		Using table Stg_IE_SvcEntityStaffedPerson_Ctl, seed tables
*				AgrStaffedPersonEffortYear and SvcEntity_AdmStaffedPerson_Map
*				with IDs for all new agreement staffed people
*
* PARAMETERS
* Name				Description					
* -------------		-----------------------------------------------------------
* n/a
*
* ERRORS USED		Description
* -------------		-----------------------------------------------------
* n/a
*
* RETURN VALUE
* Value    			Description					
* --------------	------------------------------------------------------
* @_exitStatus		An exit status of either true or false
***************************************************************************/
CREATE PROCEDURE dbo.IE_SeedSvcEntityStaffedPersonMap
AS
BEGIN

	SET NOCOUNT ON


	INSERT		SvcEntity_AdmStaffedPerson_Map (SvcEntityId, AdmPersonId, AdmStaffedPersonTypeId)
	SELECT 		SvcEntityId, AdmPersonId, AdmStaffedPersonTypeId 
	FROM		Stg_IE_SvcEntityStaffedPerson_Ctl
	WHERE 		Stg_IE_SvcEntityStaffedPerson_Ctl.IsExist = 0
		AND	Stg_IE_SvcEntityStaffedPerson_Ctl.IsValid = 1
	GROUP BY	SvcEntityStaffedPersonMapId, SvcEntityId, AdmPersonId, AdmStaffedPersonTypeId

	UPDATE Stg_IE_SvcEntityStaffedPerson_Ctl
	SET SvcEntityStaffedPersonMapId = MAP.Id
	FROM SvcEntity_AdmStaffedPerson_Map MAP
	WHERE MAP.SvcEntityId = Stg_IE_SvcEntityStaffedPerson_Ctl.SvcEntityId
	AND MAP.AdmPersonId = Stg_IE_SvcEntityStaffedPerson_Ctl.AdmPersonId
	AND Stg_IE_SvcEntityStaffedPerson_Ctl.IsExist = 0

	-- AgrStaffedPersonEffortYear
	INSERT	AgrStaffedPersonEffortYear (SvcEntityStaffedPersonMapId, AgrBudgetViewTypeId, PercentageEffort, SequenceYear)
	SELECT	SvcEntityStaffedPersonMapId, AgrBudgetViewTypeId, nPercentEffort, nYear 
	FROM	Stg_IE_SvcEntityStaffedPerson_Ctl
	WHERE 	Stg_IE_SvcEntityStaffedPerson_Ctl.IsExist = 0
	AND	Stg_IE_SvcEntityStaffedPerson_Ctl.IsValid = 1

	SET NOCOUNT OFF

END


GO